package org.querycreator.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.criterion.Criterion;
import org.junit.Ignore;
import org.querycreator.converter.ClauseAndParameters;
import org.querycreator.entity.Customer;


/**
 * 
 * @author Luciano Molinari
 *
 */
@Ignore
public class ConnectionUtils {
	private static SessionFactory sessionFactory;
	private static Connection con;
	
	static {
		sessionFactory = new Configuration().configure().buildSessionFactory();
		try {
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db_test", "root", "123456");
		} catch(Exception e) {
			e.printStackTrace();
		}
	}
	
	public static Session getSession() {
		return sessionFactory.openSession();
	}
	
	@SuppressWarnings("unchecked")
	public static <T> List<T> executeCriteria(List<Criterion> listOfCriterion) {
		Session session = getSession();
		Criteria criteria = session.createCriteria(Customer.class);
		for(Criterion c : listOfCriterion) {
			criteria.add(c);
		}
		List<T> res = criteria.list();
		
		session.close();
		
		return res;
	}
	
	public static List<Customer> executeSqlToCustomer(ClauseAndParameters clauseAndParameters) throws SQLException {
		String sql = "SELECT * FROM customer " + clauseAndParameters.getClause();
		System.out.println("Executing sql: " + sql);
		PreparedStatement stmt = con.prepareStatement(sql);
		int i = 1;
		for(Object value : clauseAndParameters.getParameters()) {
			stmt.setObject(i, value);
			i++;
		}
		List<Customer> list = new ArrayList<Customer>();
		ResultSet rs = stmt.executeQuery();
		while(rs.next()) {
			list.add(new Customer(rs.getString("name"), rs.getDate("date_of_born"), rs.getString("state"), rs.getDouble("salary")));
		}
		
		return list;
	}
	
}